//////////////////////////////////////////////////////////////////////////
// FileName:Systems
// CreateBy:DragonXiong
// CreateDate:2009-5-4
// Description:
//		Class Systems,table _Systems
//      
// Modify Log:090609:Format parameters of function and SQL Script to UPPER Case
//            090609:Add Comment
//            090615:Modify comment,add globalMessage.
//	          090616:Modify DB columns name to Model.Systems.xxx --dragon
//            090818:Add Region Node Operate for Exists() --dragon
//            090820:Modify the Lenght & DbType of Column --dragon
// Copyright:ASL-MIS
/////////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Auto.Data.DBUtility;
using Auto.Components;
using Model = Auto.Data.Systems.Model;

namespace Auto.Data.Systems.DAL
{
    /// <summary>
    /// System class for table _Systems
    /// </summary>
    public class Systems
    {
        #region --New--
        /// <summary>
        /// Initializes a new instance of the <see cref="Systems"/> class.
        /// </summary>
        public Systems()
        {
        }

        ///// <summary>
        ///// Initializes a new instance of the <see cref="_Systems"/> class.
        ///// </summary>
        ///// <param name="systems.SysID">The sys ID.</param>
        //public Systems(string sysID)
        //{
        //    GetModel(sysID);
        //}

        ///// <summary>
        ///// Selects the specified sys ID.
        ///// </summary>
        ///// <param name="systems.SysID">The sys ID.</param>
        //public Model.Systems GetModel(string sysID)
        //{
        //    Model.Systems systems = new Model.Systems();
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("SELECT  RecID,SysID,SysName,Note,Pause FROM _Systems ");
        //    strSql.Append(" WHERE SysID='" + sysID + "' ");
        //    DataTable dt = new DataTable();
        //    try
        //    {
        //        dt = DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    if (dt.Rows.Count > 0)
        //    {
        //        DataRow dr = dt.Rows[0];
        //        systems.RecID = int.Parse(dr["RecID"].ToString());

        //        systems.SysID = dr["SysID"] == DBNull.Value ? "" : dr["SysID"].ToString();
        //        systems.SysName = dr["SysName"] == DBNull.Value ? "" : dr["SysName"].ToString();
        //        systems.Note = dr["Note"] == DBNull.Value ? "" : dr["Note"].ToString();
        //        if (dr["Pause"] != DBNull.Value)
        //        {
        //            systems.Pause = Convert.ToBoolean(dr["Pause"]);
        //        }
        //    }
        //    return systems;
        //}

        /// <summary>
        /// Get Model System
        /// </summary>
        /// <param name="sysID">System ID</param>
        /// <returns>Model Systems</returns>
        public Model.Systems GetModel(string sysID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP 1 RecID,SysID,SysName,Note,Pause FROM _Systems ");
            strSql.Append(" WHERE Pause = 0 AND SysID=@SysID ");
            SqlParameter[] parameters = {
					new SqlParameter("@SysID", SqlDbType.VarChar,20)};
            parameters[0].Value = sysID;

            Model.Systems model = new Model.Systems();
            DataSet ds = DbHelper.ExecuteQuery(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr=ds.Tables[0].Rows[0];
                model.RecID=StrHelper.GetIntValue(dr[Model.Systems.FIELD_RecID]);
                model.SysID = StrHelper.GetStringValue(dr[Model.Systems.FIELD_SysID]);
                model.SysName = StrHelper.GetStringValue(dr[Model.Systems.FIELD_SysName]);
                model.Note = StrHelper.GetStringValue(dr[Model.Systems.FIELD_Note]);
                model.Pause = StrHelper.GetBoolValue(dr[Model.Systems.FIELD_Pause]);


                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion --New--

        #region --Operate--
        /// <summary>
        /// Existses the specified rec ID.
        /// </summary>
        /// <param name="sysID">The System ID.</param>
        /// <returns>bool</returns>
        public bool Exists(string sysID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM _Systems");
            strSql.Append(" WHERE SysID=@SysID ");
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@SysID", SqlDbType.VarChar,20)};
                parameters[0].Value = sysID;

                return DbHelper.Exists(strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }



        }
        #endregion --Operate--

        #region --SQL--
        ///// <summary>
        ///// Inserts this instance.
        ///// </summary>
        ///// <returns>bool</returns>
//        public bool Insert(Model.Systems systems)
//        {
//            bool result = false;
//            if (string.IsNullOrEmpty(systems.SysID))
//            {
//                return result;
//            }
//            string sql = string.Format(" SysID = '{0}'", systems.SysID);
//            try
//            {
//                sql = string.Format(@"INSERT into _Systems(SysID,SysName,Note,Pause) 
//                                    VALUES('{0}','{1}','{2}','{3}')", systems.SysID, systems.SysName, systems.Note, systems.Pause);
//                result = DbHelper.ExecuteNonQuery(sql) > 0;
//            }
//            catch (Exception ex)
//            {
//                throw (ex);
//            }
//            return result;
//        }

        /// <summary>
        /// Insert System
        /// </summary>
        /// <param name="model">System</param>
        /// <returns>bool</returns>
        public bool Insert(Model.Systems model)
        {
            int i = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO _Systems(");
            strSql.Append("SysID,SysName,Note,Pause)");
            strSql.Append(" VALUES (");
            strSql.Append("@SysID,@SysName,@Note,@Pause)");
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@SysID", SqlDbType.VarChar,20),
					new SqlParameter("@SysName", SqlDbType.NVarChar,50),
					new SqlParameter("@Note", SqlDbType.NVarChar,200),
					new SqlParameter("@Pause", SqlDbType.Bit,1)};
                parameters[0].Value = model.SysID;
                parameters[1].Value = model.SysName;
                parameters[2].Value = model.Note;
                parameters[3].Value = model.Pause;

               i= DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
               if (i == 0)
               {
                   throw new Exception(Resources.GetResource(GlobalResources.GlobalMessage.InsertError, this.GetType()));
               }
               else
               {
                   return true;
               }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            
        }
        ///// <summary>
        ///// Deletes the specified System.
        ///// </summary>
        ///// <param name="systems.SysID">The sys ID.</param>
        ///// <returns>bool</returns>
        //public bool Delete(string sysID)
        //{

        //    string sql = string.Format(@"DELETE FROM _Systems WHERE SysID = '{0}'", sysID);
        //    try
        //    {
        //        return DbHelper.ExecuteNonQuery(sql) > 0;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }

        //}

        /// <summary>
        /// Delete System By System ID
        /// </summary>
        /// <param name="sysID">System ID</param>
        /// <returns>bool</returns>
        public bool Delete(string sysID)
        {
            int i = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM _Systems ");
            strSql.Append(" WHERE SysID=@SysID ");
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@SysID", SqlDbType.VarChar,20)};
                parameters[0].Value = sysID;

                i = DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
                if (i == 0)
                {
                    throw new Exception(Resources.GetResource(GlobalResources.GlobalMessage.DeleteError, this.GetType()));
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        ///// <summary>
        ///// Updates this instance.
        ///// </summary>
        ///// <returns>bool</returns>
//        public bool Update(Model.Systems systems)
//        {
//            string sql = string.Format(@"UPDATE _Systems set SysName='{0}',Note='{1}',Pause='{2}'
//                                    WHERE SysID = '{3}'  ", systems.SysName, systems.Note, systems.Pause, systems.SysID);
//            try
//            {
//                return DbHelper.ExecuteNonQuery(sql) > 0;
//            }
//            catch (Exception ex)
//            {
//                throw (ex);
//            }
//        }

        /// <summary>
        /// Update Systems
        /// </summary>
        /// <param name="model">System</param>
        /// <returns>bool</returns>
        public bool Update(Auto.Data.Systems.Model.Systems model)
        {
            int i = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE _Systems SET ");
            strSql.Append("SysName=@SysName,");
            strSql.Append("Note=@Note,");
            strSql.Append("Pause=@Pause");
            strSql.Append(" WHERE SysID=@SysID ");
            try
            {
                SqlParameter[] parameters = {
					new SqlParameter("@RecID", SqlDbType.Int,4),
					new SqlParameter("@SysID", SqlDbType.VarChar,20),
					new SqlParameter("@SysName", SqlDbType.NVarChar,50),
					new SqlParameter("@Note", SqlDbType.NVarChar,200),
					new SqlParameter("@Pause", SqlDbType.Bit,1)};
                parameters[0].Value = model.RecID;
                parameters[1].Value = model.SysID;
                parameters[2].Value = model.SysName;
                parameters[3].Value = model.Note;
                parameters[4].Value = model.Pause;

               i=  DbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
               if (i == 0)
               {
                   throw new Exception(Resources.GetResource(GlobalResources.GlobalMessage.UpdateError, this.GetType()));
               }
               else
               {
                   return true;
               }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion --SQL--

        #region --GetData--
        /// <summary>
        /// Get Systems
        /// </summary>
        /// <returns>Datatable Systems</returns>
        public DataTable GetSystems()
        {
            string sql = @"SELECT RecID,SysID,SysName,Note 
                           FROM _Systems 
                           WHERE Pause=0";
            try
            {
                return DbHelper.ExecuteQuery(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// Get List
        /// </summary>
        /// <param name="strWhere">field1 (=,LIKE...) '' (AND/OR..)  fields2 (=,LIKE...) '' ... </param>
        /// <returns>Systems</returns>
        public DataTable GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT RecID,SysID,SysName,Note ");
			strSql.Append(" FROM _Systems ");
            strSql.Append(" WHERE Pause=0");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" AND "+strWhere);
			}
			return DbHelper.ExecuteQuery(strSql.ToString()).Tables[0];
		}
        #endregion --GetData--



    }
}